/**************************************************************************************************
	$Header: /pub/cvsroot/mydns/src/mydns/db.c,v 1.15 2002/11/12 06:35:08 bboy Exp $
	db.c: Functions for dealing with the database tables, etc.

	Copyright (C) 2002  Don Moore <bboy@bboy.net>

	This program is free software; you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation; either version 2 of the License, or
	(at Your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program; if not, write to the Free Software
	Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
**************************************************************************************************/

#include "named.h"


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_CONNECT
	Connect to the database.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void
db_connect(void)
{
	u_char *host = conf_get(&Conf, "db-host");
	u_char *password = conf_get(&Conf, "db-password");
	u_char *user = conf_get(&Conf, "db-user");
	u_char *database = conf_get(&Conf, "database");

	sql_open(user, password, host, database);
}
/*--- db_connect() ------------------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_OUTPUT_CREATE_TABLES
	Output SQL statements to create tables and exit.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void
db_output_create_tables(void)
{
	load_config();

	/* Header */
	printf("--\n");
	printf("--  Table layouts for "PACKAGE_STRING" ("PACKAGE_DATE")\n");
	printf("--  "PACKAGE_COPYRIGHT"\n");
	printf("--\n");
	printf("--  You might create these tables with a command like:\n");
	printf("--\n");

#if USE_PGSQL
	printf("--    $ %s --create-tables | psql -h HOST -U USER DATABASE\n", progname);
#else
	printf("--    $ %s --create-tables | mysql -hHOST -p -uUSER DATABASE\n", progname);
#endif
	printf("--\n");
	printf("--\n\n");

	/* Zone/SOA table */
	printf("--\n--  Table structure for table '%s' (zones of authority)\n--\n", mydns_soa_table_name);

#if USE_PGSQL
	printf("
CREATE TABLE %s (
  id      SERIAL NOT NULL PRIMARY KEY,
  origin  VARCHAR(78) NOT NULL,
  ns      VARCHAR(255) NOT NULL,
  mbox    VARCHAR(255) NOT NULL,
  serial  INTEGER NOT NULL default 1,
  refresh INTEGER NOT NULL default %u,
  retry   INTEGER NOT NULL default %u,
  expire  INTEGER NOT NULL default %u,
  minimum INTEGER NOT NULL default %u,
  ttl     INTEGER NOT NULL default %u,
  UNIQUE  (origin)
);
\n",
	mydns_soa_table_name,
	DNS_DEFAULT_REFRESH,
	DNS_DEFAULT_RETRY,
	DNS_DEFAULT_EXPIRE,
	DNS_DEFAULT_MINIMUM,
	DNS_DEFAULT_TTL
);
#else
	printf("
CREATE TABLE IF NOT EXISTS %s (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  origin     CHAR(78) NOT NULL,
  ns         CHAR(255) NOT NULL,
  mbox       CHAR(255) NOT NULL,
  serial     INT UNSIGNED NOT NULL default '1',
  refresh    INT UNSIGNED NOT NULL default '%u',
  retry      INT UNSIGNED NOT NULL default '%u',
  expire     INT UNSIGNED NOT NULL default '%u',
  minimum    INT UNSIGNED NOT NULL default '%u',
  ttl        INT UNSIGNED NOT NULL default '%u',
  UNIQUE KEY (origin)
) TYPE=MyISAM;
\n",
	mydns_soa_table_name,
	DNS_DEFAULT_REFRESH,
	DNS_DEFAULT_RETRY,
	DNS_DEFAULT_EXPIRE,
	DNS_DEFAULT_MINIMUM,
	DNS_DEFAULT_TTL
);
#endif

	/* Resource record table */
	printf("--\n--  Table structure for table '%s' (resource records)\n--\n", mydns_rr_table_name);

#if USE_PGSQL
printf("
CREATE TABLE %s (
  id     SERIAL NOT NULL PRIMARY KEY,
  zone   INTEGER NOT NULL,
  name   VARCHAR(63) NOT NULL,
  type   VARCHAR(5) NOT NULL CHECK (type='A' OR type='AAAA' OR type='CNAME' OR type='MX' OR type='NS' OR type='TXT'),
  data   VARCHAR(255) NOT NULL,
  aux    INTEGER NOT NULL default 0,
  ttl    INTEGER NOT NULL default %u,
  UNIQUE (zone,name,type,data),
  FOREIGN KEY (zone) REFERENCES soa (id) ON DELETE CASCADE
);
\n",
	mydns_rr_table_name,
	DNS_DEFAULT_TTL
);
#else
printf("
CREATE TABLE IF NOT EXISTS %s (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  zone       INT UNSIGNED NOT NULL,
  name       CHAR(63) NOT NULL,
  type       ENUM('A','AAAA','CNAME','MX','NS','TXT'),
  data       CHAR(255) NOT NULL,
  aux        INT UNSIGNED NOT NULL,
  ttl        INT UNSIGNED NOT NULL default '%u',
  UNIQUE KEY rr (zone,name,type,data)
) TYPE=MyISAM;
\n",
	mydns_rr_table_name,
	DNS_DEFAULT_TTL
);
#endif

	/* PTR table */
	printf("--\n--  Table structure for table '%s'\n--\n", mydns_ptr_table_name);

#if USE_PGSQL
	printf("
CREATE TABLE %s (
  id     SERIAL NOT NULL PRIMARY KEY,
  ip     INTEGER NOT NULL,
  name   VARCHAR(255) NOT NULL,
  ttl    INTEGER NOT NULL default %u,
  UNIQUE (ip)
);
\n",
	mydns_ptr_table_name,
	DNS_DEFAULT_TTL
);

#else
	printf("
CREATE TABLE IF NOT EXISTS %s (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip         INT UNSIGNED NOT NULL,
  name       CHAR(255) NOT NULL,
  ttl        INT UNSIGNED NOT NULL default '%u',
  UNIQUE KEY (ip)
) TYPE=MyISAM;
\n",
	mydns_ptr_table_name,
	DNS_DEFAULT_TTL
);
#endif
	exit(EXIT_SUCCESS);
}
/*--- db_output_create_tables() -----------------------------------------------------------------*/


#ifdef notdef
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_SQL_NUMROWS
	Returns the number of rows from a specified result.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
static int
db_sql_numrows(const char *fmt, ...)
{
	va_list ap;
	char query[256];
	size_t querylen;
	SQL_RES *res = NULL;
	int rv;

	va_start(ap, fmt);
	querylen = vsnprintf(query, sizeof(query), fmt, ap);
	va_end(ap);

	if (!(res = sql_query(sql, query, querylen)))
		ErrSQL(sql, "%s", _("database error"));
	rv = sql_num_rows(res);
	sql_free(res);
	return (rv);
}
/*--- db_sql_numrows() --------------------------------------------------------------------------*/
#endif


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_CHECK_COLUMN
	Makes sure the specified column name exists in the specified table for the database.
	Fatal if the column does not exist.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
static void
db_check_column(database, table, name)
	u_char *database;
	u_char *table;
	u_char *name;
{
	if (!sql_iscolumn(sql, table, name))
	{
		Warnx(_("Required column `%s' in table `%s' (database `%s') not found or inaccessible"),
				name, table, database);
		Warnx(_("Do you need to create the tables in the `%s' database?"), database);
		Warnx(_("You can run `%s --create-tables' to output appropriate SQL commands"), progname);
		exit(EXIT_FAILURE);
	}
}
/*--- db_check_column() -------------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_VERIFY_TABLE
	Verifies each column in a comma-separated list.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
static void
db_verify_table(database, table, columns)
	u_char *database;
	u_char *table;
	u_char *columns;
{
	char fields[80], *f = fields, *name;

	/* Check that the table itself exists */
	if (!sql_istable(sql, table))
	{
		Warnx(_("Required table `%s' in database `%s' not found or inaccessible"), table, database);
		Warnx(_("Do you need to create the tables in the `%s' database?"), database);
		Warnx(_("You can run `%s --create-tables' to output appropriate SQL commands"), progname);
		exit(EXIT_FAILURE);
	}

	/* Check each field in field list */
	strncpy(fields, columns, sizeof(fields)-1);
	while ((name = strsep(&f, ",")))
		db_check_column(database, table, name);
}
/*--- db_verify_table() -------------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_CHECK_OPTIONAL
	Check optional columns.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void
db_check_optional(void)
{
	/* Optional columns in soa table */
	if (mydns_set_soa_use_active(sql) > 0)
		Verbose(_("optional \"active\" column found in \"%s\" table"), mydns_soa_table_name);
	if (mydns_set_soa_use_xfer(sql) > 0)
		Verbose(_("optional \"xfer\" column found in \"%s\" table"), mydns_soa_table_name);

	/* Optional columns in rr table */
	if (mydns_set_rr_use_active(sql) > 0)
		Verbose(_("optional \"active\" column found in \"%s\" table"), mydns_rr_table_name);

	/* Optional columns in ptr table */
	if (mydns_set_ptr_use_active(sql) > 0)
		Verbose(_("optional \"active\" column found in \"%s\" table"), mydns_ptr_table_name);
}
/*--- db_check_optional() -----------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_VERIFY_TABLES
	Makes sure the required tables exist and that we can read from them.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void
db_verify_tables(void)
{
	u_char *host = conf_get(&Conf, "db-host");
	u_char *database = conf_get(&Conf, "database");
	u_char *password = conf_get(&Conf, "db-password");
	u_char *user = conf_get(&Conf, "db-user");

	sql_open(user, password, host, database);

	/* XXX: Fix this - check existence of database etc */

	db_verify_table(database, mydns_soa_table_name, MYDNS_SOA_FIELDS);
	db_verify_table(database, mydns_rr_table_name, MYDNS_RR_FIELDS);
	db_verify_table(database, mydns_ptr_table_name, MYDNS_PTR_FIELDS);

	db_check_optional();

	sql_close(sql);
}
/*--- db_verify_tables() ------------------------------------------------------------------------*/

/* vi:set ts=3: */
/* NEED_PO */
